package com.dx.mes.service.spc.service;

import com.dx.common.table.ppt.Bis_m_item;
import com.dx.common.table.ppt.Spc_ret_mfg_data_zr;
import com.dx.mes.common.dao.IBisMItemDao;
import com.dx.mes.common.dao.ISpcRetMfgDataDiffDao;
import com.dx.mes.common.transcoder.util.AppContext;
import com.dx.mes.common.transcoder.util.JacksonUtil;
import com.dx.mes.common.transcoder.util.LogUtils;
import com.dx.mes.service.spc.tx.Xpcmsdt.XpcmsrdtI;
import com.dx.mes.service.spc.tx.Xpcmsdt.XpcmsrdtIA;
import com.dx.mes.service.spc.tx.Xpcmsdt.XpcmsrdtO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import static com.dx.mes.common.bean.def.GenericDef.RETURN_CODE_OK;

/**
 * Created by XXD on 2018/6/12.
 */
@Service
//@PropertySource("classpath:filePaths.properties")
public class AccessParseService {
    private LogUtils logUtils;
    @Autowired
    ISpcRetMfgDataDiffDao spcRetMfgDataDiffDao;
    @Autowired
    private IFqprepdataService fqprepdataService;
    @Autowired
    private IBisMItemDao bisMItemDao;


    //jdbc连接access数据库
    public Connection connectSQLserver(String Url, String Username, String Password) {
        Connection conn = null;
        try {
            // 加载数据库驱动，注册到驱动管理器
            Class.forName("com.hxtt.sql.access.AccessDriver").newInstance();
            // 数据库连接字符串
            String url = Url;
            // 数据库用户名
            String username = Username;
            // 数据库密码
            String password = Password;
            // 创建Connection连接
            conn = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    //先查edc表中最新的一笔数据的时间
    public List<Spc_ret_mfg_data_zr> findLastTimeRptTime(String toolId) {
        String hql = "From Spc_ret_mfg_data_zr where tool_id_fk ='" + toolId + "'";
        hql += " and mes_id_fk = '" + "ZRAUTOQZ" + "'";
        hql += " order by rpt_timestamp desc";
        List<Spc_ret_mfg_data_zr> spcRetMfgDataZrList = spcRetMfgDataDiffDao.find(hql);
        return spcRetMfgDataZrList;
    }

    //查配置表
    public List<Bis_m_item> queryItems(String tool_id_fk, String mes_id_fk) {
        String hql2 = "From Bis_m_item where tool_id_fk = '" + tool_id_fk + "'";
        hql2 += " and mes_id_fk = '" + mes_id_fk + "'";
        hql2 += " order by data_seq";
        List<Bis_m_item> bis_m_items = bisMItemDao.find(hql2);
        return bis_m_items;
    }


    //组trx
    public XpcmsrdtI sendTrx(ResultSet rs, String dataGroup, String toolId, String fabId, String line, String opeId) throws Exception {
        // 实例化Book对象
        XpcmsrdtI trxI = new XpcmsrdtI();
        List<XpcmsrdtIA> iaryList = new ArrayList<>();
        XpcmsrdtIA iary = new XpcmsrdtIA();

        //量测名称
        iary.setData_group(dataGroup);
        iary.setData_value(String.valueOf(rs.getFloat("减膜量(mg)")));
        iary.setData_group_seq("1");
        iary.setData_type("03");
        iaryList.add(iary);

        String date = rs.getString("日期");
        String time = rs.getString("时间");
        String dateTime = date + " " + time;
        Timestamp timestamp = Timestamp.valueOf(dateTime);
        trxI.setIary(iaryList);
        trxI.setTool_id(toolId);
        trxI.setFabId(fabId);
        trxI.setLineId(line);
        trxI.setOpe_id(opeId);
        trxI.setRep_unit_fk("M");//上报单元 P:process M:messure
        trxI.setData_pat_fk("00");//上报属性 00,01,02,03   00:EDC
        trxI.setMes_id_fk("ZRAUTOQZ");
        trxI.setData_cnt(String.valueOf(iaryList.size()));
        trxI.setIary(iaryList);
        trxI.setFlag("A");
        //TODO
        trxI.setEvt_timestamp(timestamp);//量测时间
        return trxI;
    }


    //定时读取制绒线的sqlserver数据库
    public void readZRserver() {
        logUtils = new LogUtils(AccessParseService.class);
        logUtils.info("开始捞access数据！");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String dataGroup = "去重";
        String toolId = "TEXT09";
        String fabId = "电池厂";
        String line = "101A-1";
        String opeId = "制绒";
        List<Spc_ret_mfg_data_zr> spcRetMfgDataZrList = findLastTimeRptTime(toolId);
        Timestamp lastTimestap = null;
        List<Bis_m_item> bisMItemList = queryItems(toolId, "ZRAUTOQZ");
        //未设置配置项则不做任何操作
        if (bisMItemList.size() == 0) {
            return;
        }

        Connection conn = null;
        String url = "jdbc:Access:///" + "/home/test2/zhirong09/chengzhong.mdb";
        String user = "";
        String password = "";
        conn = connectSQLserver(url, user, password);
        //TODO 判断连接有没有成功
        try {
            // 获取Statement
            Statement stmt = conn.createStatement();
            // 添加SQL语句
            String sql = "select *  FROM 称重信息表 where 日期 = '2018-06-15'";
            // 执行查询
            ResultSet rs = stmt.executeQuery(sql);
            // 实例化List对象
            List<XpcmsrdtI> xpcmsrdtIList = new ArrayList<XpcmsrdtI>();
            if (spcRetMfgDataZrList != null && spcRetMfgDataZrList.size() > 0) {
                lastTimestap = spcRetMfgDataZrList.get(0).getRpt_timestamp();
                // 判断光标向后移动，并判断是否有效
                while (rs.next()) {
                    String date = rs.getString("日期");
                    String time = rs.getString("时间");
                    String dateTime = date + " " + time;
                    Timestamp tableTimestamp = Timestamp.valueOf(dateTime);
                    //如果sqlserver时间小于 最后上报的时间，则过滤掉
                    if (tableTimestamp.compareTo(lastTimestap) <= 0) {
                        continue;
                    }
                    XpcmsrdtI trx = sendTrx(rs, dataGroup, toolId, fabId, line, opeId);
                    xpcmsrdtIList.add(trx);
                }
            } else {
                while (rs.next()) {
                    //全存
                    String date = rs.getString("日期");
                    String time = rs.getString("时间");
                    String dateTime = date + " " + time;
                    Timestamp tableTimestamp = Timestamp.valueOf(dateTime);
                    logUtils.info("时间:------------" + dateTime);
                    //yyyy-MM-dd HH:mm:ss
                    String midtime = "2018-06-15 12:00:00";
                    Timestamp ts = Timestamp.valueOf(midtime);
                    if (tableTimestamp.compareTo(ts) > 0) {
                        XpcmsrdtI trx = sendTrx(rs, dataGroup, toolId, fabId, line, opeId);
                        xpcmsrdtIList.add(trx);
                    }
                }
            }

            //循环送service 单点上报
            if (xpcmsrdtIList.size() > 0) {
                logUtils.info("单点上报！！！！");
                for (int i = 0; i < xpcmsrdtIList.size(); i++) {
                    XpcmsrdtO xpcmsrdtO = new XpcmsrdtO();
                    xpcmsrdtO = autoReportSPC(xpcmsrdtIList.get(i));
                    if (!xpcmsrdtO.getRtn_code().equals(RETURN_CODE_OK)) {
                        logUtils.error(xpcmsrdtO.getRtn_mesg());
                        break;
                    }
                }
            }
        } catch (Exception e) {
            logUtils.error(e.toString());
        }
    }

    //上报spc
    private XpcmsrdtO autoReportSPC(XpcmsrdtI inTrx) throws Exception {
        XpcmsrdtI xpcmsrdtI = inTrx;
        XpcmsrdtO xpcmsrdtO = new XpcmsrdtO();
        xpcmsrdtO.setTrx_id("FQPEDCDAT");
        xpcmsrdtO.setTrx_id("O");

        String strOutTrx = fqprepdataService.subMainProc(AppContext.getCurrEventNumber(),
                JacksonUtil.toJSONStr(xpcmsrdtI));
        xpcmsrdtO = JacksonUtil.fromJson(strOutTrx, XpcmsrdtO.class);
        return xpcmsrdtO;
    }

}
